   *************************************************************************************
   * Figure 1 
   *************************************************************************** **********
   
   ***data paths 
   global archive /Volumes/Sans titre/DATA_MICROEU
   global posted /Volumes/Sans titre/DATA_MICROEU/Posting
   global data /Users/Mathilde/Dropbox/Posted_workers/Data
     
   
   **output paths
  global replication_outputs_a /Users/Mathilde/Dropbox/Posted_workers/Replication/TNT_final/Replication_package/Appendix_figures
  global replication_estimates /Users/Mathilde/Dropbox/Posted_workers/Replication/TNT_final/Replication_package/Main_estimates
  global replication_estimates_a /Users/Mathilde/Dropbox/Posted_workers/Replication/TNT_final/Replication_package/Appendix_estimates
  global replication_outputs /Users/Mathilde/Dropbox/Posted_workers/Replication/TNT_final/Replication_package/Main_figures
  
**************************************
   

  *****************************Assembling descriptives statistics for Figure 1**************************
  * all datasets are described in details in the read_me file 
  ***************************************************************************************
  
  ****************************************************************************
  * stock of foreign EU workers at a given point in time (Panel B, Figure 1)
  ****************************************************************************

   *Raw confidential LFS micro data files provided by Eurostat 
   *Citizenship variable available starting in 1998 (with gap in 2003 for most countries)
   
   *EU member states, 1998-2003
   foreach var in lu fr de be nl it ie pt uk es gr dk at fi se{
   forval x=1998/2003{
   use "$archive/LFS 3/`var'`x'.dta", clear
   keep if age>17
   keep if age<66
   g foreigner=1
   replace foreigner=0 if national=="000" 
   replace foreigner=0 if missing(national)
   g foreigneu=0
   replace foreigneu=1 if national=="111" 
   g y_mob=sum(coeff) if foreigner==1
   g y_mob2=sum(coeff) if foreigneu==1
   g y_tot=sum(coeff) 
   g share=y_mob/y_tot
   g share2=y_mob2/y_tot
   destring year, replace
   collapse y_mob y_mob2 share2 y_tot share year, by(country )
   save "$posted/`var'`x'_citizenship.dta", replace
   }
   }
   
   *EU member states, 2004-2006
   *data in 2003 is missing in many countries
   *note: citizenship variable categories changed in 2004 (more categories)
   foreach var in lu fr de be nl it ie pt uk es gr dk at fi se hu pl cz ee lt lv cy si sk{
   forval x=2004/2006{
   use "$archive/LFS 3/`var'`x'.dta", clear
   keep if age>17
   keep if age<66
   g foreigner=1
   replace foreigner=0 if national=="000-OWN COUNTRY" 
   replace foreigner=0 if missing(national)
   replace foreigner=0 if national=="NO ANSWER"

   g foreigneu=0
   replace foreigneu=1 if national=="001-EU15" 
   replace foreigneu=1 if national=="002-NMS10" 
   replace foreigneu=1 if national=="003-NMS3" 
  
   g y_mob=sum(coeff) if foreigner==1
   g y_mob2=sum(coeff) if foreigneu==1
   g y_tot=sum(coeff) 
   g share=y_mob/y_tot
   g share2=y_mob2/y_tot
   destring year, replace
   collapse y_mob y_mob2 share2 y_tot share year, by(country )
   save "$posted/`var'`x'_citizenship.dta", replace
   }
   }
   
   *EU member states, 2007-2012
   foreach var in lu fr de be nl it ie pt uk es gr dk at fi se hu pl cz ee lt lv cy si sk ro bg{
   forval x=2007/2012{
   use "$archive/LFS 3/`var'`x'.dta", clear
   keep if age>17
   keep if age<66
   g foreigner=1
   replace foreigner=0 if national=="000-OWN COUNTRY" 
   replace foreigner=0 if missing(national)
   replace foreigner=0 if national=="NO ANSWER"

   g foreigneu=0
   replace foreigneu=1 if national=="001-EU15" 
   replace foreigneu=1 if national=="002-NMS10" 
   replace foreigneu=1 if national=="003-NMS3" 
  
   g y_mob=sum(coeff) if foreigner==1
   g y_mob2=sum(coeff) if foreigneu==1
   g y_tot=sum(coeff) 
   g share=y_mob/y_tot
   g share2=y_mob2/y_tot
   destring year, replace
   collapse y_mob y_mob2 share2 y_tot share year, by(country )
   save "$posted/`var'`x'_citizenship.dta", replace
   }
   }
   
   *EU member states, 2013-2016

   foreach var in lu fr de be nl it ie pt uk es gr dk at fi se hu pl cz ee lt lv cy si sk ro bg hr{
   forval x=2013/2016{
   use "$archive/LFS 3/`var'`x'.dta", clear
   keep if age>17
   keep if age<66
   g foreigner=1
   replace foreigner=0 if national=="000-OWN COUNTRY" 
   replace foreigner=0 if missing(national)
   replace foreigner=0 if national=="NO ANSWER"

   g foreigneu=0
   replace foreigneu=1 if national=="001-EU15" 
   replace foreigneu=1 if national=="002-NMS10" 
   replace foreigneu=1 if national=="003-NMS3" 
  
   g y_mob=sum(coeff) if foreigner==1
   g y_mob2=sum(coeff) if foreigneu==1
   g y_tot=sum(coeff) 
   g share=y_mob/y_tot
   g share2=y_mob2/y_tot
   destring year, replace
   collapse y_mob y_mob2 share2 y_tot share year, by(country )
   save "$posted/`var'`x'_citizenship.dta", replace
   }
   }
   
   use "$posted/lu1998_citizenship.dta", clear
   forval x=1998/2003{
   *careful, one duplicate for LU 98 created, drop later
   foreach var in lu fr de be nl it ie pt uk es gr dk at fi se{
   append using "$posted/`var'`x'_citizenship.dta"
   }
   }
   forval x=2004/2006{
   foreach var in lu fr de be nl it ie pt uk es gr dk at fi hu se pl si sk lt lv ee cz  cy{
   append using "$posted/`var'`x'_citizenship.dta"
   }
   }
   forval x=2007/2012{
   foreach var in lu fr de be nl it ie pt uk es gr dk at fi hu se pl si sk lt lv ee cz ro bg cy{
   append using "$posted/`var'`x'_citizenship.dta"
   }
   }
   forval x=2013/2016{
   foreach var in lu fr de be nl it ie pt uk es gr dk at fi hu se pl si sk lt lv ee cz hr ro bg cy{
   append using "$posted/`var'`x'_citizenship.dta"
   }
   }
   
   duplicates tag country year, g(tag)
   tab country if tag>0
   tab year if tag>0
   duplicates drop country year, force 
   
   
    egen total_eu=sum(y_tot), by(year)
    egen mob_eu=sum(y_mob), by(year)
    egen mob_eu2=sum(y_mob2), by(year)
	
  g share_eu=mob_eu/total_eu
  replace share_eu=share_eu*100
  g share_eu2=mob_eu2/total_eu
  replace share_eu2=share_eu2*100
  twoway (connected share_eu2 year if year!=2003), ///
  graphregion(fcolor(white) lcolor(white)) xlabel(1987 (1) 2016, angle(vertical)) ///
  xtitle("") ytitle(Migration Rate (%))
  save "$posted/foreignstocks.dta", replace
  collapse (mean) share_eu2, by(year)
  replace share_eu2=. if year==2003 
  save "$posted/foreignstocks_series.dta", replace
 
   
   

   
   ******************************************************************
   *. Figure 1 
   *******************************************************************

*Panel A
*data collected from EC on total number of posted workers (adjusted by duration)   
import excel "/Users/Mathilde/Dropbox/Posted_workers/Data/scaleofposting_EU.xlsx", sheet("total") firstrow clear
reshape long n, j(year) i(country)
destring n, replace
replace n=n/1000
twoway (connected n year if country=="total_def" ), ylabel(0 (500) 2000) ///
graphregion(fcolor(white) lcolor(white)) xlabel(1988 (1) 2019, angle(vertical)) ///
xline(2004, lpattern(dash)) xtitle("") ytitle("Workers posted abroad to supply services" "thousands, full time equivalents") ///
ttext(2006 2000 "Start of Posting Policy Expansion" "For New Member States (NMS)", box margin(small) fcolor(white))
graph export "$replication_outputs/Fig1a.pdf", replace
   
   
   
   
 *Panel B
 *Import same serie than before (divided by active age pop in the EU) and merge with the EU-LFS based measure of foreigners' stock in the EU   
 
use "$posted/foreignstocks_series.dta", clear 
merge 1:1 year using "$posted/pw_fte.dta"
import excel "/Users/Mathilde/Dropbox/Posted_workers/Data/scaleofposting_EU.xlsx", sheet("comparaison") firstrow clear
reshape long n, j(year) i(country)
destring n, replace
keep if country=="posted"
merge 1:1 year using "$posted/foreignstocks_series.dta"

 twoway (connected n year ) ///
(connected share_eu2 year, lpattern(dash) ), ylabel(0 (1) 4) ///
graphregion(fcolor(white) lcolor(white)) xlabel(1988 (1) 2019, angle(vertical)) ///
xline(2004, lpattern(dash)) xtitle("") ytitle("Number of individuals" "% of working age population") ///
legend(label(1 "Posted workers (flows, FTE)") label(2 "EU migrants (stocks, numbers)") cols(1) ring(0) pos(11))
graph export "$replication_outputs/Fig1b.pdf", replace
  
 
  
  *Panel C
  *Compute all summary statistics for year 2016 
  
  global archive /Volumes/Sans titre/DATA_MICROEU
  global posted /Volumes/Sans titre/DATA_MICROEU/Posting
  global outputs /Users/Mathilde/Dropbox/Posted_workers/Paper/Figures
  global data /Users/Mathilde/Dropbox/Posted_workers/Data

  global nms PL SK CZ EE SI LT LV HU RO BG HR AT BE BG CH DE DK ES FI FR GR IE IT LI LU NL NO PT SE UK

   foreach var in lu fr de be nl it ie pt uk es gr dk at fi se pl sk cz ee si lt lv hu ro bg hr mt cy{
   forval x=2016/2016{
   use "$archive/LFS 3/`var'`x'.dta", clear
   keep if age>17
   keep if age<66
   destring ilostat, replace
   keep if ilostat==1
   g mig=0
   foreach y in $nms{
   replace mig=1*1000*coeff if COUNTR1Y=="`y'"
   }
   replace mig=0 if COUNTR1Y==country
   g mig_eu=0
   replace mig_eu=1*coeff*1000 if national=="001-EU15"
   replace mig_eu=1*coeff*1000 if national=="002-NMS10"
   replace mig_eu=1*coeff*1000 if national=="003-NMS3"
   encode yearesid, g(residence)
   g mig_eu_rec=mig_eu
   replace mig_eu_rec=0 if residence>11
   replace mig_eu_rec=0 if residence==1
  
   
   egen tot=sum(coeff*1000)
   collapse (sum) mig mig_eu mig_eu_rec (mean) tot, by(year country)  
   save "$posted/`var'`x'_summary.dta", replace
   }
   }
   
   use "$posted/lu2016_summary.dta", clear
   foreach var in fr de be nl it ie pt uk es gr dk at fi se pl sk cz ee si lt lv hu ro bg hr mt cy{
   append using "$posted/`var'2016_summary.dta",
   }
   collapse (sum) mig_eu tot mig_eu_rec mig 
   g year=2016
   save "$posted/fig1.dta", replace 
   
   
   
   global nms PL SK CZ EE SI LT LV HU RO BG HR AT BE BG CH DE DK ES FI FR GR IE IT LI LU NL NO PT SE UK
   foreach var in lu fr de be nl it ie pt uk es gr dk at fi se pl sk cz ee si lt lv hu ro bg hr mt cy{
   forval x=2016/2016{
   use "$archive/LFS 3/`var'`x'.dta", clear
   keep if age>17
   keep if age<66
   destring ilostat, replace
   *keep if ilostat==1
   g mig=0
   foreach y in $nms{
   replace mig=1*1000*coeff if COUNTR1Y=="`y'"
   }
   replace mig=0 if COUNTR1Y==country
   egen tot=sum(coeff*1000)
   collapse (sum) mig (mean) tot, by(year country)  
   save "$posted/`var'`x'_summarymig.dta", replace
   }
   }
   
   use "$posted/lu2016_summarymig.dta", clear
   foreach var in fr de be nl it ie pt uk es gr dk at fi se pl sk cz ee si lt lv hu ro bg hr mt cy{
   append using "$posted/`var'2016_summarymig.dta",
   }
   collapse (sum) mig
   save "$posted/fig1_c.dta", replace 
   g year=2016 
   rename mig mig2
   merge 1:1 year using "$posted/fig1.dta"
   drop _m 
   merge 1:1 year using "$posted/pw_fte.dta"
   keep if _m==3
   label var mig2 "Within-EU immigration flows"
   label var mig_eu "Stock of EU immigrants"
   label var mig_eu_rec "Stock of recent EU immigrants (moved in past 10 years)"
   label var n "Posted workers (full time equivalents)"
   foreach y in mig2 mig_eu mig_eu_rec mig n{
   	replace `y'=`y'/1000000
   	replace `y'=round(`y',.1)
   }
   drop tot _merge 
   order year 
   *Figure 1, Panel C 
   export excel "$replication_outputs/Figure1.xlsx" , firstrow(varlabels) replace
   
   
   
   
   
   
   
  
  
  
  